package com.rt.easyjava.base.biz1.service.impl;

import com.google.common.base.Strings;
import com.rt.easyjava.base.biz1.bean.ImportBizData;
import com.rt.easyjava.base.biz1.service.intf.DataBizService;
import com.rt.easyjava.common.base.Result;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.builder.annotation.ProviderMethodResolver;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
@Transactional
public class DataServiceImpl implements DataBizService, ProviderMethodResolver {

    @Resource
    private SqlSessionTemplate sessionTemplate;

    /**
     * 将Excel 中的数据导入到数据库中
     *
     * @param data 附加参数
     * @param file Excel文件
     * @return 响应结果
     */
    public Result<Map> importData(ImportBizData data, MultipartFile file) {
        if (null == file || 0 >= file.getSize()) {
            return Result.builder().fault("上传文件为空，请重新上传！");
        }
        if ((!file.getOriginalFilename().endsWith("xls")) && (!file.getOriginalFilename().endsWith("xlsx"))) {
            return Result.builder().fault("文件格式不正确，仅支持xls/xlsx文件");
        }
        StringBuilder sb = new StringBuilder();
        InputStream inputStream = null;
        XSSFWorkbook workbook = null;
        try {
            inputStream = file.getInputStream();
            workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(data.getSheetNum());
            for (Row cells : sheet) {
                String sqlColumns = "";
                //当前行小于参数指定的数据起始行时，跳过
                if (cells.getRowNum() < data.getStartRow()) {
                    continue;
                }
                List<String> columnData = new ArrayList();
                for (Cell cell : cells) {
                    String value = cell.getStringCellValue();
                    if (!Strings.isNullOrEmpty(value)) {
                        value = value.trim();
                    }
                    value = "'" + value + "'";
                    columnData.add(value);
                }
                sqlColumns = columnData.toString().replace("[", "(");
                sqlColumns = sqlColumns.replace("]", ")");
                sqlColumns = sqlColumns.concat(",");
                sb.append(sqlColumns);
            }
            log.debug("封装好的数据为：{}", sb);
            int count = batchInsert(data, sb);
            return Result.builder().success("操作成功！共新增" + count + "条数据！");
        } catch (IOException e) {
            log.error("操作失败！错误信息:{}", e);
            return Result.builder().fault("文件解析发生错误！");
        } catch (SQLException e) {
            log.error("操作失败！错误信息:{}", e);
            return Result.builder().fault("数据导入发生错误！");
        }finally {
           if (workbook!=null) {
               try {
                   workbook.close();
               } catch (IOException e) {
                   e.printStackTrace();
               }
           }
           if (inputStream!=null) {
               try {
                   inputStream.close();
               } catch (IOException e) {
                   e.printStackTrace();
               }
           }
        }
    }


    private int batchInsert(ImportBizData data, StringBuilder dataToAdd) throws SQLException {
        SqlSession session = this.sessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH);
        int count = 0;
        Connection connection = session.getConnection();
        Statement stmt = connection.createStatement();
        String columns = data.getDbColumnName().toString().replace("[", "(");
        columns = columns.replace("]", ")");
        String sqlValues = dataToAdd.toString();
        if (sqlValues.endsWith(",")) {
            sqlValues = sqlValues.substring(0, sqlValues.length() - 1);
        }
        stmt.addBatch("insert into " + data.getTableName() + columns + "values" + sqlValues);
        int[] res = stmt.executeBatch();
        count = res.length;
        stmt.closeOnCompletion();
        if (data.isCommitOnException()) {
            session.commit();
        } else {
            session.rollback();
        }
        return count;
    }
}
